package com.cm309.hospital.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cm309.hospital.model.BookingStep2;

public class BookingStep2Dao {
	public static MyDataSource mds = new MyDataSource();
	
	public List<BookingStep2> queryAll() {
		List<BookingStep2> doctors = new ArrayList<BookingStep2>();

		try {
			
			Connection conn = mds.getConnection();
			PreparedStatement ps = null;
			try {
				ps = conn.prepareStatement("SELECT a.doctor_id,a.doctor_name,a.specialty,a.sex,b.subject_name FROM Doctor as a LEFT JOIN Subject b on b.subject_id = a.subject_id ");
				ResultSet rs = ps.executeQuery();

				while (rs.next()) {
					BookingStep2 c = new BookingStep2(rs.getShort("doctor_id"),
							rs.getString("doctor_name"), rs.getString("specialty"),
							rs.getString("sex"));
					doctors.add(c);
				}

			} finally {
				ps.close();
			}
			return doctors;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return null;
	}

	public BookingStep2 querySingle(Short subject_id) {
		BookingStep2 doctor = null;
		try {
			Connection conn = mds.getConnection();
			PreparedStatement ps = null;
			try {
				ps = conn.prepareStatement("SELECT a.doctor_id,a.doctor_name,a.specialty,a.sex FROM Doctor as a WHERE a.subject_id = ?");
				ps.setShort(1, subject_id);
				ResultSet rs = ps.executeQuery();

				if (rs.next()) {
					doctor = new BookingStep2(rs.getShort("doctor_id"),
							rs.getString("doctor_name"), rs.getString("specialty"),
							rs.getString("sex"));
				}

			} finally {
				ps.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return doctor;
		
	}
	
	public List<BookingStep2> queryById(Short subject_id) {
		List<BookingStep2> doctors = new ArrayList<BookingStep2>();

		try {
			
			Connection conn = mds.getConnection();
			PreparedStatement ps = null;
			try {
				ps = conn.prepareStatement("SELECT a.doctor_id,a.doctor_name,a.specialty,a.sex FROM Doctor as a WHERE a.subject_id = ?");
				ps.setShort(1, subject_id);
				ResultSet rs = ps.executeQuery();

				while (rs.next()) {
					BookingStep2 c = new BookingStep2(rs.getShort("doctor_id"),
							rs.getString("doctor_name"), rs.getString("specialty"),
							rs.getString("sex"));
					doctors.add(c);
				}

			} finally {
				ps.close();
			}
			return doctors;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return null;
	}

}
